#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Date;
use Config::IniFiles;
use Time::Local;
use XML::LibXML;

require "/var/www/cgi-bin/gnugraph/rcoos/graphAll.lib";
require "/var/www/cgi-bin/gnugraph/rcoos/timeZone.lib";
my $graph_config="/var/myscheduler/graph.xml";
my $xp_graph = XML::LibXML->new->parse_file($graph_config);

my $station=$ARGV[0];
my $datatype=$ARGV[1];
my $sort=$ARGV[2];
my $beginDate=$ARGV[3];
my $endDate=$ARGV[4];
if($endDate){$endDate="$endDate 23:59:59";}
my @stations=split("\,",$station);
my @datatypes=split("\,",$datatype);
my @sort=split("-",$sort);

my $sortstring ="order by ".join(" ",@sort); 

my ($sqlsta,$sth1,$sql,$sth,@array1);

#my $dbname="/var/www/html/RCOOS/website/latest.db";
my $tmp="/var/www/html/images/images/";
my $html=$tmp."comparetemp.php";
if(-e $html){
    system("rm $html");
}
open (TEMPFILE1,">$html");


#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
            #        { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}
 my $dbh=getdbConn();
 if($dbh==0){ die "Cannot connect to database!\n";}

my $stationstring="";
 for(my $i=0;$i<@stations;$i++){
    if($i>0){$stationstring=$stationstring." or "}
    $stationstring=$stationstring."short_name='$stations[$i]'";
 }

  
 # get basic info about the stations
 $sqlsta="select short_name,platform_handle,fixed_longitude,fixed_latitude,description,long_name,begin_date,end_date from platform where $stationstring";

 print TEMPFILE1 "<table cellpadding=1 width=80% cellspacing=1><tr bgcolor=#ffffdd><td>Station</td><td>Longitude</td><td>Latitude</td><td>Description</td><td>Start Operating</td><td>End Date</td></tr>";
 $sth1 = $dbh->prepare($sqlsta);
 $sth1->execute();
 $stationstring="";
 my %platform;
 my %mystation;
 while (my @row = $sth1->fetchrow_array) {
     print TEMPFILE1 "<tr bgcolor=#eeeeee><td>&nbsp;$row[0]</td><td>&nbsp;$row[2]</td><td>&nbsp;$row[3]</td><td>&nbsp;$row[4]&nbsp;&nbsp;&nbsp;$row[5]</td><td>&nbsp;$row[6]</td><td>&nbsp;$row[7]</td></tr>";
     $stationstring=$stationstring."platform_handle='$row[1]' or ";
     $platform{$row[0]}=$row[1];
     $mystation{$row[1]}=$row[0];
 }
 $stationstring="(".substr($stationstring,0,-4).")";
 $sth1->finish();
 print TEMPFILE1 "</table><br><br>";
 
 print TEMPFILE1 "Query Results: <br>";



 for(my $a=0;$a<@datatypes;$a++){
    $sql="select m_type.row_id,obs_type.standard_name,uom_type.standard_name from m_type
         left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
         left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
         left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
         where obs_type.row_id=$datatypes[$a]";

    $sth = $dbh->prepare($sql);
    $sth->execute();
 
 
    my($formula,$typeid,$title,$units,$dir,$standardname,$uom);
 
    while (my @row = $sth->fetchrow_array) {
      $typeid=$row[0];
      $standardname=$row[1];
      ($uom,$formula)=uom_convert($standardname,$row[2]);
  
      my @array1=split(/_/,$standardname);
      $standardname="";
      for(my $i=0;$i<@array1;$i++){
         $array1[$i]=ucfirst($array1[$i]);
         $standardname=$standardname.$array1[$i]." ";
      }
    }#end of while loop
      
      $sortstring="order by platform_handle,sensor_id,m_date asc";
         
      my $stationstring="(platform_handle='$platform{$stations[0]}'";
      for(my $i=1;$i<@stations;$i++){
           $stationstring=$stationstring." or platform_handle='$platform{$stations[$i]}'";
      }
      $stationstring=$stationstring.")";


          
     $sqlsta="select m_value,qc_level,substr(m_date,1,16),platform_handle,sensor_id,m_z from multi_obs where m_date>='$beginDate' and m_date<='$endDate' and $stationstring and m_type_id=$typeid and (qc_level!=1 or qc_level is null) and m_value is not null $sortstring";

      $sth1 = $dbh->prepare($sqlsta);
      $sth1->execute();
            
      #my (@mdatearray,$count);
      #my (%array1,%array2,%array3,%array4,%array5,%array6,%array7,%array8);
      my (@station1,@station2,@graph_data_file,$p_platform,$p_sensor,$filecount,$sensorcount,$current_data_file);
         $filecount=0;
         $sensorcount=0;
         $p_platform="";
      while (my @row1 = $sth1->fetchrow_array) {
               $row1[0]=value_convert($formula,$row1[0]);
               $row1[2]=substr($row1[2],0,10)." ".substr($row1[2],11,5);

               if(($row1[3] ne $p_platform)||($p_platform eq "")){
                    
                    if($filecount>0){
                       close(TEMPFILE);
                    }
                    if(($row1[5]<=0)&&($row1[5]>-9999)){
                        $row1[5]=0-$row1[5];
                        $row1[5]=sprintf("%.2f",$row1[5]);
                        push(@station1,$mystation{$row1[3]}." ($row1[5] m)");
                    }  
                    else {
                        push(@station1,$mystation{$row1[3]});
                    }                 
                    
                    push(@station2,$mystation{$row1[3]});
                    
                    $graph_data_file[$filecount] =$tmp.'gnuplot_'.int(rand(10000000));    
                    open (TEMPFILE,">$graph_data_file[$filecount]");
                    $filecount++;
                    $sensorcount=0;
               }
               else {
                    if($row1[4] ne $p_sensor){
                        
                       close(TEMPFILE);
                       if($sensorcount<2){ 
                        if(($row1[5]<=0)&&($row1[5]>-9999)){
                           $row1[5]=0-$row1[5];
                           $row1[5]=sprintf("%.2f",$row1[5]);
                           push(@station1,$mystation{$row1[3]}." ($row1[5] m)");
                        }   
                        else {
                           push(@station1,$mystation{$row1[3]});
                        }   
                        $graph_data_file[$filecount] =$tmp.'gnuplot_'.int(rand(10000000));                     
                         
                        open (TEMPFILE,">$graph_data_file[$filecount]");
                        $filecount++;
                        }
                        $sensorcount++;
                        #print "sensorcount \t $sensorcount\n";
                    }
                     
               }
               if($sensorcount<=2){
                   print TEMPFILE $row1[2]."\t".$row1[0]."\n";
               }        
      
               $p_platform=$row1[3];
               $p_sensor=$row1[4];
      }
      if($filecount>0){ close(TEMPFILE);}      

      for(my $a=0;$a<@stations;$a++){
            if(grep {$_ eq $stations[$a]} @station2){;}           
            else { 
                print TEMPFILE1 "No $standardname data found for $stations[$a] on the specified time period.<br>";
            }
      }
 
     
      #start graphing for this data type 
      $dir=$tmp."test".$a.".png";
      
      if(-e $dir){system("rm $dir");}
     
  
      if($filecount>0){
         
         $title="$standardname ($beginDate - $endDate)";
         graphMul($dir,$standardname,$uom,\@graph_data_file,\@station1);
         
         print TEMPFILE1 "<img src=images/images/test".$a.".png?x=<?=uniqid((double)microtime()*1000000,1);?>><br>";
         print TEMPFILE1 "<br>";
      }
      $sth->finish();
 
} # end of datatype loop


 close(TEMPFILE1);

 undef $sth1;
 undef $sth;
 $dbh->disconnect();

 sub uom_convert {

 my ($obs_type,$uom) = @_;

 my $unit_conversion = '';

 ##CONFIG_START
 #conversions for the following which were labeled wrong on the way in to db
 if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
 if ($uom eq 'millibar') { $uom = 'mb'; }
 if ($uom eq 'percent_saturation') { $uom = 'percent'; }
 ##CONFIG_END

  my $unit_conversion = $xp_graph->findvalue('//observation_list/observation[@standard_name="'.$obs_type.'.'.$uom.'"]/standard_uom_en');
  if ($unit_conversion eq '') { print "not found: $obs_type $uom \n"; return ($uom,$unit_conversion); }

  my $y_title="";
  my $conversion_formula="";
  $y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/y_title');
  if($y_title eq ""){$y_title=$uom;}
  $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/conversion_formula');

 #unit conversion using supplied equation(e.g. celcius to fahrenheit)
 #my $conversion_string = $conversion_formula;
  #  $conversion_string =~ s/var1/$measurement/g;
 #my $conversion_val = eval $conversion_string;

 return ($y_title,$conversion_formula);

}

sub value_convert {

 my ($conversion_string,$measurement) = @_;
    $conversion_string =~ s/var1/$measurement/g;
 my $conversion_val = eval $conversion_string;
 return $conversion_val; 
}


